package com.lagou.dao;

import com.lagou.entity.ClassGrade;
import com.lagou.entity.PageBean;
import com.lagou.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassGradeDaoImpl implements ClassGradeDao {
    @Override
    public List<ClassGrade> ClassGradeShow(PageBean pageBean) {
        List<ClassGrade> classList = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // 1、获取Connection对象
            connection = DbUtil.getConnetion();
            // 2、使用StringBuilder类型的对象来描述SQL语句的目的在于后续可以改变
            StringBuilder stringBuilder = new StringBuilder("select * from t_class");
            // 3、当输入分页的需求时进行分页查询，于是拼接SQL语句
            if (pageBean != null) {
                stringBuilder.append(" limit "+pageBean.getStart()+","+pageBean.getRow());
            }
            //4、获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            //5、将结果集中的数据取出并放到list集合中返回
            classList = new ArrayList<>();
            while(resultSet.next()){
                ClassGrade classGrade = new ClassGrade(resultSet.getInt("id"), resultSet.getString("name"),
                        resultSet.getString("grade"),
                        resultSet.getString("teacher"),
                        resultSet.getString("slogan"),
                        resultSet.getInt("number"));
                System.out.println(classGrade);
                classList.add(classGrade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return classList;

    }

    @Override
    public int ClassGradeAdd(ClassGrade classGrade) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DbUtil.getConnetion();

            String sql = "insert into t_class values (NULL, '"+classGrade.getName()+
                    "','"+classGrade.getGrade()+"','"+classGrade.getTeacher()+"','"+ classGrade.getSlogan()+ "','"+
                    classGrade.getNumber()+"');";
            System.out.println(sql);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DbUtil.closeResource(connection, preparedStatement);
        }
        return 1;
    }

    @Override
    public int ClassGradeDelete(String[] ids) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DbUtil.getConnetion();

            for(int i=0; i<ids.length; i++) {
                String sql = "delete from t_class where id= '" + ids[i] +"';";
                System.out.println(sql);
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.executeUpdate();
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DbUtil.closeResource(connection, preparedStatement);
        }
        return 1;
    }

    @Override
    public int ClassGradeModify(ClassGrade classGrade) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DbUtil.getConnetion();

            String sql = "update t_class set name = '"+classGrade.getName()+"', grade='"+classGrade.getGrade()+
                    "', teacher='"+classGrade.getTeacher()+"', slogan='"+ classGrade.getSlogan()+"'," +
                    "number='"+ classGrade.getNumber()+"' where id='"+classGrade.getId()+"';";
            System.out.println(sql);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DbUtil.closeResource(connection, preparedStatement);
        }
        return 1;
    }

    @Override
    public ClassGrade ClassGradeFind(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ClassGrade classGrade = null;
        try {
            // 1、获取Connection对象
            connection = DbUtil.getConnetion();
            // 2、sql语句
            String sql = "select * from t_class where id='"+id+"';";

            //3、获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            //4、将结果集中的数据返回
            while(resultSet.next()){
                classGrade = new ClassGrade(resultSet.getInt("id"), resultSet.getString("name"),
                        resultSet.getString("grade"),
                        resultSet.getString("teacher"),
                        resultSet.getString("slogan"),resultSet.getInt("number"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return classGrade;
    }
}
